CREATE SEQUENCE SEQ_ID_POBYTU
		MINVALUE 1 
		MAXVALUE 999999999999999999 
		START WITH 1 
		INCREMENT BY 1 
		NOCACHE
;

create or replace
TRIGGER TRG_POBYT_INSERT 
		BEFORE INSERT ON POBYT 
			FOR EACH ROW 
			BEGIN 
				IF :new.ID_POBYTU IS NULL THEN
					SELECT SEQ_ID_POBYTU.nextval INTO :new.ID_POBYTU FROM DUAL; 
				END IF; 
			END; 
	/
  COMMIT;
  
CREATE SEQUENCE SEQ_ID_TAXY
		MINVALUE 1 
		MAXVALUE 999999999999999999 
		START WITH 1 
		INCREMENT BY 1 
		NOCACHE
;

create or replace
TRIGGER TRG_TAXA_INSERT 
		BEFORE INSERT ON TAXA 
			FOR EACH ROW 
			BEGIN 
				IF :new.ID_TAXY IS NULL THEN
					SELECT SEQ_ID_TAXY.nextval INTO :new.ID_TAXY FROM DUAL; 
				END IF; 
			END; 
	/
  COMMIT;
  
CREATE SEQUENCE SEQ_ID_STRAVOVANI
		MINVALUE 1 
		MAXVALUE 999999999999999999 
		START WITH 1 
		INCREMENT BY 1 
		NOCACHE
;

create or replace
TRIGGER TRG_STRAVOVANI_INSERT 
		BEFORE INSERT ON STRAVOVANI 
			FOR EACH ROW 
			BEGIN 
				IF :new.ID_STRAVOVANI IS NULL THEN
					SELECT SEQ_ID_STRAVOVANI.nextval INTO :new.ID_STRAVOVANI FROM DUAL; 
				END IF; 
			END; 
	/
  COMMIT;  

  CREATE SEQUENCE SEQ_ID_SLUZBY
	    MINVALUE 1 
	    MAXVALUE 999999999999999999 
	    START WITH 1 
	    INCREMENT BY 1 
	    NOCACHE
;

create or replace
TRIGGER TRG_SLUZBA_INSERT 
	    BEFORE INSERT ON SLUZBA
	        FOR EACH ROW 
	        BEGIN 
	            IF :new.ID_SLUZBY IS NULL THEN
	                SELECT SEQ_ID_SLUZBY.nextval INTO :new.ID_SLUZBY FROM DUAL; 
	            END IF; 
	        END; 
	/
  COMMIT;





CREATE OR replace PROCEDURE Projekt_vloz_pobyt(p_id_taxy       IN pobyt.id_taxy%TYPE,
                                               p_prijezd       IN pobyt.prijezd%TYPE,
                                               p_odjezd        IN pobyt.odjezd%TYPE,
                                               p_id_pokoje     IN pokoj.id_pokoje%TYPE,
                                               p_rodne_cislo   IN host.rodne_cislo%TYPE,
                                               p_id_stravovani IN stravovani.id_stravovani%TYPE)
AS
  v_pocet_dni       INT;
  v_cena_stravovani INT;
  v_celkova_suma    INT;
  v_cena_pokoje     INT;
  v_cena_taxy       INT;
  v_id_pobytu       INT;
  v_id_pobytu_col   INT;
  date_colision EXCEPTION;
BEGIN
  INSERT INTO pobyt
              (id_taxy,
               prijezd,
               odjezd,
               celkova_suma,
               zaplaceno)
  VALUES      (p_id_taxy,
               p_prijezd,
               p_odjezd,
               0,
               0);

  v_id_pobytu := seq_id_pobytu.currval;

  INSERT INTO objednavka_pokoje
              (id_pobytu,
               id_pokoje)
  VALUES     (v_id_pobytu,
              p_id_pokoje);

  INSERT INTO objednavka_stravovani
              (id_pobytu,
               id_stravovani,
               mnozstvi_stravovani)
  VALUES     (v_id_pobytu,
              p_id_stravovani,
              1);

  INSERT INTO seznam_hostu
              (id_pobytu,
               rodne_cislo)
  VALUES     (v_id_pobytu,
              p_rodne_cislo);

  SELECT odjezd - prijezd
  INTO   v_pocet_dni
  FROM   pobyt
  WHERE  id_pobytu = v_id_pobytu;

  SELECT t.cena_taxy
  INTO   v_cena_taxy
  FROM   taxa t,
         pobyt p
  WHERE  p.id_taxy = t.id_taxy
         AND p.id_pobytu = v_id_pobytu;

  SELECT p.cena_pokoje
  INTO   v_cena_pokoje
  FROM   pokoj p,
         objednavka_pokoje o
  WHERE  p.id_pokoje = o.id_pokoje
         AND o.id_pobytu = v_id_pobytu;

  SELECT SUM(s.cena_stravovani * o.mnozstvi_stravovani)
  INTO   v_cena_stravovani
  FROM   stravovani s,
         objednavka_stravovani o
  WHERE  o.id_stravovani = s.id_stravovani
         AND o.id_pobytu = v_id_pobytu;

  v_celkova_suma := ( v_cena_pokoje + v_cena_taxy + v_cena_stravovani ) * v_pocet_dni;

  UPDATE pobyt
  SET    celkova_suma = v_celkova_suma
  WHERE  id_pobytu = v_id_pobytu;

  --KONTROLA OBSAZENOSTI
  SELECT p.id_pobytu
  INTO   v_id_pobytu_col
  FROM   objednavka_pokoje o,
         pobyt p
  WHERE  p_prijezd < odjezd
         AND p_odjezd > prijezd
         AND p.id_pobytu = o.id_pobytu
         AND o.id_pokoje = p_id_pokoje;

  dbms_output.Put_line('Pokoj je volny');

  COMMIT;
EXCEPTION
  WHEN too_many_rows THEN
             dbms_output.Put_line('Pokoj je jiz obsazen');

             RAISE too_many_rows;

             ROLLBACK; WHEN OTHERS THEN
             dbms_output.Put_line('Chyba pri vkladani pobytu');

             Raise_application_error(-20101, 'Chyba pri vkladani pobytu');

             ROLLBACK;
END; 





CREATE OR replace PROCEDURE Projekt_add_host(p_rodne_cislo IN host.rodne_cislo%TYPE,
                                             p_jmeno       IN host.jmeno%TYPE,
                                             p_prijmeni    IN host.prijmeni%TYPE,
                                             p_ulice       IN host.ulice%TYPE,
                                             p_mesto       IN host.mesto%TYPE,
                                             p_psc         IN host.psc%TYPE,
                                             p_telefon     IN host.telefon%TYPE,
                                             p_email       IN host.email%TYPE)
AS
BEGIN
  INSERT INTO host
              (rodne_cislo,
               jmeno,
               prijmeni,
               ulice,
               mesto,
               psc,
               telefon,
               email)
  VALUES      (p_rodne_cislo,
               p_jmeno,
               p_prijmeni,
               p_ulice,
               p_mesto,
               p_psc,
               p_telefon,
               p_email);
EXCEPTION
  WHEN dup_val_on_index THEN
             dbms_output.Put_line('Rodne cislo neni jedinecne');

             RAISE dup_val_on_index;

             ROLLBACK; WHEN OTHERS THEN
             dbms_output.Put_line('Nepodarilo se vlozit hosta');

             Raise_application_error(-20101, 'Chyba pri vkladani hosta');

             ROLLBACK;
END; 





CREATE OR replace PROCEDURE Projekt_kontrola_obsazenosti(
p_id_pokoje IN pokoj.id_pokoje%TYPE,
p_prijezd   IN pobyt.prijezd%TYPE,
p_odjezd    IN pobyt.odjezd%TYPE)
AS
  v_id_pobytu INTEGER;
  date_colision EXCEPTION;
BEGIN
  SELECT p.id_pobytu
  INTO   v_id_pobytu
  FROM   objednavka_pokoje o,
         pobyt p
  WHERE  ( p_prijezd < odjezd
           AND p_odjezd > prijezd )
         AND p.id_pobytu = o.id_pobytu
         AND o.id_pokoje = p_id_pokoje;

  RAISE date_colision;
EXCEPTION
  WHEN date_colision THEN
             dbms_output.Put_line(v_id_pobytu); WHEN too_many_rows THEN
             dbms_output.Put_line('Pokoj je obsazen'); WHEN no_data_found THEN
             NULL;
END; 






REATE OR replace PROCEDURE Projekt_smaz_pobyt(p_id_pobytu IN
pobyt.id_pobytu%TYPE)
AS
BEGIN
  DELETE FROM objednavka_pokoje
  WHERE  id_pobytu = p_id_pobytu;

  DELETE FROM objednavka_stravovani
  WHERE  id_pobytu = p_id_pobytu;

  DELETE FROM objednavka_sluzby
  WHERE  id_pobytu = p_id_pobytu;

  DELETE FROM seznam_hostu
  WHERE  id_pobytu = p_id_pobytu;

  DELETE FROM pobyt
  WHERE  id_pobytu = p_id_pobytu;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
             dbms_output.Put_line('Chyba pri mazani pobytu');

             Raise_application_error(-20101, 'Chyba pri mazani pobytu');

             ROLLBACK;
END; 




CREATE OR replace PROCEDURE Projekt_spocti_cenu(p_id_pobytu IN
pobyt.id_pobytu%TYPE)
AS
  v_pocet_dni       INT;
  v_cena_stravovani INT;
  v_cena_sluzeb     INT;
  v_celkova_suma    INT;
  v_cena_pokoje     INT;
  v_cena_taxy       INT;
BEGIN
  SELECT odjezd - prijezd
  INTO   v_pocet_dni
  FROM   pobyt
  WHERE  id_pobytu = p_id_pobytu;

  SELECT t.cena_taxy
  INTO   v_cena_taxy
  FROM   taxa t,
         pobyt p
  WHERE  p.id_taxy = t.id_taxy
         AND p.id_pobytu = p_id_pobytu;

  SELECT p.cena_pokoje
  INTO   v_cena_pokoje
  FROM   pokoj p,
         objednavka_pokoje o
  WHERE  p.id_pokoje = o.id_pokoje
         AND o.id_pobytu = p_id_pobytu;

  SELECT SUM(s.cena_sluzby * o.mnozstvi_sluzeb)
  INTO   v_cena_sluzeb
  FROM   sluzba s,
         objednavka_sluzby o
  WHERE  o.id_sluzby = s.id_sluzby
         AND o.id_pobytu = p_id_pobytu;

  SELECT SUM(s.cena_stravovani * o.mnozstvi_stravovani)
  INTO   v_cena_stravovani
  FROM   stravovani s,
         objednavka_stravovani o
  WHERE  o.id_stravovani = s.id_stravovani
         AND o.id_pobytu = p_id_pobytu;

  v_celkova_suma := ( v_cena_pokoje + v_cena_taxy + v_cena_stravovani ) *
                    v_pocet_dni;

  IF v_cena_sluzeb IS NOT NULL THEN
    v_celkova_suma := v_celkova_suma + v_cena_sluzeb;
  END IF;

  UPDATE pobyt
  SET    celkova_suma = v_celkova_suma
  WHERE  id_pobytu = p_id_pobytu;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
             dbms_output.Put_line('Chyba pri pocitani celkove sumy');

             Raise_application_error(-20101, 'Chyba pri pocitani celkove sumy');

             ROLLBACK;
END; 